package com.matt.repository;

import com.matt.controller.api.domin.Thermometer;
import com.matt.domain.ChildActivity;
import com.matt.foundation.query.ChildrenActiveQuery;
import com.matt.foundation.vo.ChildActivityVo;
import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.List;


public interface ChildActivityRepository
        extends JpaRepository<ChildActivity, String>, JpaSpecificationExecutor<ChildActivity>
{


//    @Query(value = "SELECT ca.title, ca.classIds, GROUP_CONCAT(c.name SEPARATOR ', ') as classNames, " +
//            "ca.type, ca.address, ca.startTime, ca.endTime, ca.deadlineTime, ca.content, ca.remark, " +
//            "ca.areaId, ca.sourceType, ca.createTime, ca.id " +
//            "FROM child_activity ca " +
//            "LEFT JOIN classes c ON FIND_IN_SET(c.id COLLATE utf8mb4_unicode_ci, ca.classIds COLLATE utf8mb4_unicode_ci) > 0 " +
//            "WHERE (:areaId IS NULL OR ca.areaId = :areaId) " +
//            "GROUP BY ca.id", nativeQuery = true)
//    List<Object[]> findByQueryNew(@Param("areaId") String areaId);

    @Query(value =
            "SELECT " +
                    "ca.title, " +
                    "ca.classIds, " +
                    "ca.type, " +
                    "ca.address, " +
                    "ca.startTime, " +
                    "ca.endTime, " +
                    "ca.deadlineTime, " +
                    "ca.content, " +
                    "ca.remark, " +
                    "ca.areaId, " +
                    "ca.sourceType, " +
                    "ca.createTime, " +
                    "ca.id " +
                    "FROM child_activity ca " +
                    "WHERE (:areaId IS NULL OR ca.areaId = :areaId) " +
                    "ORDER BY ca.createTime DESC", nativeQuery = true)
    List<Object[]> findByQueryNew(
            @Param("areaId") String areaId
    );
//    @Query(value = "SELECT COUNT(DISTINCT ca.id) FROM child_activity ca " +
//            "LEFT JOIN classes c ON FIND_IN_SET(c.id, ca.classIds) > 0 " +
//            "WHERE (:areaId IS NULL OR ca.areaId = :areaId)", nativeQuery = true)
//    Long findCountByQueryNew(@Param("areaId") String areaId);

    @Query(value =
            "SELECT COUNT(DISTINCT ca.id) " +
                    "FROM child_activity ca " +
                    "WHERE (:areaId IS NULL OR ca.areaId = :areaId)",
            nativeQuery = true)
    Long findCountByQueryNew(@Param("areaId") String areaId);

    @Query(value =
            "SELECT " +
                    "ca.title, " +
                    "ca.classIds, " +
                    "ca.type, " +
                    "ca.address, " +
                    "ca.startTime, " +
                    "ca.endTime, " +
                    "ca.deadlineTime, " +
                    "ca.content, " +
                    "ca.remark, " +
                    "ca.areaId, " +
                    "ca.sourceType, " +
                    "ca.createTime, " +
                    "ca.id " +
                    "FROM child_activity ca " +
                    "WHERE (:areaId IS NULL OR ca.areaId = :areaId)  And" +
                    "where (:classId is null or ca.classIds like '%' :classId '%')" +
                    "ORDER BY ca.createTime DESC", nativeQuery = true)
    List<Object[]> findByQueryNew1(String areaId, List<String> classId);
}
